﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using TenderSampleApp.ReportingWebService;
using System.Data.OleDb;
using System.Data;
using System.IO;
using System.Transactions;
using System.Text.RegularExpressions;
using TenderSampleApp.Areas.DAK.Models;

namespace TenderSampleApp.Models
{

    public class DAL
    {
        public class JSON_Region
        {
            public int ID { get; set; }
            public string Name { get; set; }
        }
        public class JSON_District
        {
            public int ID { get; set; }
            public string Name { get; set; }
        }
        public class JSON_City
        {
            public int ID { get; set; }
            public string Name { get; set; }
        }
        public class JSON_Street
        {
            public int ID { get; set; }
            public string Name { get; set; }
        }
        public class JSON_Person
        {
            public string LastName { get; set; }
            public string Name { get; set; }
            public string Surname { get; set; }
            public int PersonBirthdayYear { get; set; }
            public string PersonPhoneNumber { get; set; }
            public string PersonPostIndex { get; set; }
            public string PersonIssuedBy { get; set; }
            public string PersonNumberСertificate { get; set; }
            public string PersonDateOfIssueСertificate { get; set; }
            public string PersonSeriesСertificate { get; set; }
            public string PersonRegion { get; set; }
            public string PersonDistrict { get; set; }
            public string PersonCity { get; set; }
            public string PersonStreet { get; set; }
            public string PersonBuild { get; set; }
            public int? PersonApartment { get; set; }
        }
        public class JSON_Jurist
        {
            public string Code { get; set; }
            public string FullName { get; set; }
            public string PostIndex { get; set; }
            public string Region { get; set; }
            public string District { get; set; }
            public string City { get; set; }
            public string Street { get; set; }
            public string Build { get; set; }
            public int? Apartment { get; set; }
            public string PhoneNumber { get; set; }
            public double ShareStateProperty { get; set; }
            public double TotalAssets { get; set; }
            public double CostAcquisition { get; set; }
            public double CostAcquisitionInPercent { get; set; }
            public bool IsInvestmentFund { get; set; }
        }
        public class JSON_Auction
        {
            public int ID { get; set; }
            public string DateStart { get; set; }
            public string DateEnd { get; set;}
        }
        TenderDBEntities entitty = new TenderDBEntities();
        #region Select Queries

        public List<MainReportModel>  GetMainReportResult()
        {
           

            var itmlist= 
                          (from vatcol in entitty.VAT_AuctionCollection
                           join auc in entitty.Auctions on vatcol.AuctionID equals auc.ID
                           join v in entitty.VATs on vatcol.VATID equals v.ID
                           where auc.ID == 1
                           select new 
                           {
                               Indexes=v.REG_OWN,
                               ID = v.ID,
                               Apartment = v.Apartment,
                               BranchCode = v.BranchCode,
                               Build = v.Build,
                               Street = v.Street.Name,
                               City = v.Street.City.Name,
                               District = v.Street.City.District.Name,
                               Code = v.Code,
                               FORMVIP = v.FORMVIP,
                               ManagerFIO = v.ManagerFIO,
                               MIN_CODE = v.MIN_CODE,
                               Name = v.Name,
                               NUMEMPL = v.NUMEMPL,
                               PhoneNumber = v.PhoneNumber,
                               REG_OWN = v.REG_OWN,
                               REGISTRAT = v.REGISTRAT,
                               PostIndex = v.PostIndex,
                               TotalAssets = v.TotalAssets,
                               StockQuantity = vatcol.StockQuantity,
                               Region = v.Street.City.District.Region.Name,
                               TYPEPRIV = v.TYPEPRIV,
                               RatedCost = vatcol.RatedCost,
                               BeginCost=vatcol.BeginCost 
                               
                               
                           }).ToList();
            List<MainReportModel> mrmlist = new List<MainReportModel>();
                        foreach(var tbl in itmlist)
                        {
                            MainReportModel mrm = new MainReportModel();
                            mrm.VATInfo = tbl.Name + "; адреса: " + new VATModel() { Region = tbl.Region, District = tbl.District, City = tbl.City, Street = tbl.Street, Build = tbl.Build, Apartment = tbl.Apartment }.FullAddress;
                            
                            mrm.CodeAndBranchCode = tbl.Code + "\n" + tbl.BranchCode;
                            mrm.TotalAssetsAndStockQuantity = tbl.StockQuantity + "\n" + tbl.TotalAssets;
                            mrm.RatedCostAndBeginCost = tbl.RatedCost + "\n" + tbl.BeginCost;

                            //кількість заявок
                            mrm.CountPhysRequest = new DAKDAL().GetCountOfPhysicalPersonRequest(tbl.ID);  
                            mrm.CountJurRequest = new DAKDAL().GetCountOfJuridicalPersonRequest(tbl.ID);
                            mrm.CountAmountRequest = mrm.CountPhysRequest + mrm.CountJurRequest;
                            //----------------

                            //завялено коштів
                            mrm.SumPhysRequest = new DAKDAL().GetSumAllPhysicalPersonRequest(tbl.ID);
                            mrm.SumJurRequest = new DAKDAL().GetSumAllJuridicalPersonRequest(tbl.ID);
                            mrm.SumAmountRequest = mrm.SumJurRequest + mrm.SumPhysRequest;
                            //---------------

                            //допущених заяв
                            mrm.AdmittedCountPhysRequest = new DAKDAL().GetCountOfValidPhysicalPersonRequest(tbl.ID); 
                            mrm.AdmittedCountJurRequest = new DAKDAL().GetCountOfValidJuridicalPersonRequest(tbl.ID);
                            mrm.AdmittedCountAmountRequest = mrm.AdmittedCountPhysRequest + mrm.AdmittedCountJurRequest;
                            //---------------

                            //кошти до разрах
                            mrm.PrepaySumPhysRequest = new DAKDAL().GetPrepaySumAllPhysicalPersonRequest(tbl.ID); 
                            mrm.PrepaySumJurRequest = new DAKDAL().GetPrepaySumAllJuridicalPersonRequest(tbl.ID);
                            mrm.PrepaySumAmountRequest = mrm.PrepaySumPhysRequest + mrm.PrepaySumJurRequest;
                            //---------------

                            //задов заяв
                            mrm.ValidCountPhysRequest = mrm.AdmittedCountPhysRequest; 
                            mrm.ValidCountJurRequest = mrm.AdmittedCountJurRequest;
                            mrm.ValidCountAmountRequest = mrm.AdmittedCountAmountRequest;
                            //--------------

                            //незадов заяв
                            mrm.InvalidCountJurRequest = mrm.CountJurRequest - mrm.ValidCountJurRequest;
                            mrm.InvalidCountPhysRequest = mrm.CountPhysRequest - mrm.ValidCountPhysRequest;
                            mrm.InvalidCountAmountRequest = mrm.CountAmountRequest - mrm.ValidCountAmountRequest;
                            //-------------

                            //аукціонна ціна
                            mrm.FactCost = new DAKDAL().GetFactCost(tbl.ID);
                            //--------------

                            //акцій продано
                            double soldstockpr = (mrm.PrepaySumPhysRequest) / mrm.FactCost; double soldstockjr = (mrm.PrepaySumJurRequest) / mrm.FactCost;
                            mrm.SoldStock= (int)Math.Floor(soldstockpr+soldstockjr);
                            mrm.RestStock = (tbl.StockQuantity - mrm.SoldStock);
                            //--------------

                            //кошти для повернення
                            mrm.ReturnPrepaySumPhysRequest = new DAKDAL().GetReturnedPrepaySumAllPhysicalPersonRequest(tbl.ID,(soldstockpr*mrm.FactCost));
                            mrm.ReturnPrepaySumJurRequest = new DAKDAL().GetReturnedPrepaySumAllJuridicalPersonRequest(tbl.ID, (soldstockjr * mrm.FactCost));
                            mrm.ReturnPrepaySumAmountRequest = mrm.ReturnPrepaySumPhysRequest + mrm.ReturnPrepaySumJurRequest;
                           //---------------------

                            //Сплачено
                            mrm.PaidSumPhysRequest = mrm.PrepaySumPhysRequest;
                            mrm.PaidSumJurRequest = mrm.PrepaySumJurRequest;
                            mrm.PaidSumAmountRequest = mrm.PrepaySumAmountRequest;
                             mrmlist.Add(mrm);
                        }
                        return mrmlist;
              
        }

        //public DataSet GetMainReportDataSet()
        //{
        //    List<MainReportModel> rmlist = GetMainReportResult();
        //    DataSet ds = new DataSet();
        //    DataTable table = ds.Tables.Add();

        //    table.Columns.Add(new DataColumn("VATInfo", typeof(string)));
        //    table.Columns.Add(new DataColumn("CodeAndBranchCode", typeof(string)));
        //    table.Columns.Add(new DataColumn("TotalAssetsAndStockQuantity", typeof(string)));
        //    table.Columns.Add(new DataColumn("RatedCostAndBeginCost", typeof(string)));

        //    table.Columns.Add(new DataColumn("CountPhysRequest", typeof(int)));
        //    table.Columns.Add(new DataColumn("CountJurRequest", typeof(int)));
        //    table.Columns.Add(new DataColumn("CountAmountRequest", typeof(int)));

        //    table.Columns.Add(new DataColumn("SumPhysRequest", typeof(double)));
        //    table.Columns.Add(new DataColumn("SumJurRequest", typeof(double)));
        //    table.Columns.Add(new DataColumn("SumAmountRequest", typeof(double)));

        //    table.Columns.Add(new DataColumn("AdmittedCountPhysRequest", typeof(int)));
        //    table.Columns.Add(new DataColumn("AdmittedCountJurRequest", typeof(int)));
        //    table.Columns.Add(new DataColumn("AdmittedCountAmountRequest", typeof(int)));

        //    table.Columns.Add(new DataColumn("PrepaySumPhysRequest", typeof(double)));
        //    table.Columns.Add(new DataColumn("PrepaySumJurRequest", typeof(double)));
        //    table.Columns.Add(new DataColumn("PrepaySumAmountRequest", typeof(double)));

        //    table.Columns.Add(new DataColumn("ValidCountPhysRequest", typeof(int)));
        //    table.Columns.Add(new DataColumn("ValidCountJurRequest", typeof(int)));
        //    table.Columns.Add(new DataColumn("ValidCountAmountRequest", typeof(int)));

        //    table.Columns.Add(new DataColumn("InvalidCountPhysRequest", typeof(int)));
        //    table.Columns.Add(new DataColumn("InvalidCountJurRequest", typeof(int)));
        //    table.Columns.Add(new DataColumn("InvalidCountAmountRequest", typeof(int)));

        //    table.Columns.Add(new DataColumn("ReturnPrepaySumPhysRequest", typeof(double)));
        //    table.Columns.Add(new DataColumn("ReturnPrepaySumJurRequest", typeof(double)));
        //    table.Columns.Add(new DataColumn("ReturnPrepaySumAmountRequest", typeof(double)));

        //    table.Columns.Add(new DataColumn("FactCost", typeof(double)));

        //    table.Columns.Add(new DataColumn("SoldStock", typeof(int)));
        //    table.Columns.Add(new DataColumn("RestStock", typeof(int)));

        //    table.Columns.Add(new DataColumn("PaidSumPhysRequest", typeof(double)));
        //    table.Columns.Add(new DataColumn("PaidSumJurRequest", typeof(double)));
        //    table.Columns.Add(new DataColumn("PaidSumAmountRequest", typeof(double)));
        //    foreach (MainReportModel rm in rmlist)
        //    {
        //        table.Rows.Add(rm.VATInfo, rm.CodeAndBranchCode, rm.TotalAssetsAndStockQuantity, rm.RatedCostAndBeginCost,
        //            rm.CountPhysRequest,rm.CountJurRequest, rm.CountAmountRequest,
        //            rm.SumPhysRequest, rm.SumJurRequest, rm.SumAmountRequest,
        //            rm.AdmittedCountPhysRequest, rm.AdmittedCountJurRequest, rm.AdmittedCountAmountRequest,
        //            rm.PrepaySumPhysRequest, rm.PrepaySumJurRequest, rm.PrepaySumAmountRequest,
        //            rm.ValidCountPhysRequest, rm.ValidCountJurRequest, rm.ValidCountAmountRequest,
        //            rm.InvalidCountPhysRequest, rm.InvalidCountJurRequest, rm.InvalidCountAmountRequest,
        //            rm.ReturnPrepaySumPhysRequest, rm.ReturnPrepaySumJurRequest, rm.ReturnPrepaySumAmountRequest,
        //            rm.FactCost, rm.SoldStock, rm.RestStock,
        //            rm.PaidSumPhysRequest, rm.PaidSumJurRequest, rm.PaidSumAmountRequest);
        //    }
        //    return table.DataSet;

        //}

        public JSON_Auction GetAuction(string number)
        {
            Auction auc = entitty.Auctions.Where(a => a.Number == number).FirstOrDefault();

            return auc == null ? null : new JSON_Auction() { DateEnd = auc.DateEnd.ToString("dd'/'MM'/'yyyy"), DateStart = auc.DateEnd.ToString("dd'/'MM'/'yyyy"), ID = auc.ID };
        }

        public List<JSON_Region> GetRegions(string partOfRegion)
        {
            return entitty.Regions.Where(r => r.Name.ToLower().StartsWith(partOfRegion)).Select(r => new JSON_Region { ID = r.ID, Name = r.Name }).ToList();
        }
        public List<JSON_District> GetDistricts(string partOfDistrict, int regionID)
        {
            return entitty.Districts.Where(d => d.Name.ToLower().StartsWith(partOfDistrict) && d.RegionID == regionID).Select(d => new JSON_District { ID = d.ID, Name = d.Name }).ToList();
        }
        public List<JSON_City> GetCities(string partOfCity, int districtID)
        {
            return entitty.Cities.Where(c => c.Name.ToLower().StartsWith(partOfCity) && c.DistrictID == districtID).Select(c => new JSON_City { ID = c.ID, Name = c.Name }).ToList();
        }
        public List<JSON_Street> GetStreets(string partOfStreet, int cityID)
        {
            return entitty.Streets.Where(s => s.Name.ToLower().StartsWith(partOfStreet) && s.CityID == cityID).Select(s => new JSON_Street { ID = s.ID, Name = s.Name }).ToList();
        }
        public List<string> GetPhysicalPersonLastName(string partOfLastName)
        {
            return entitty.PhysicalPersons.Where(p => p.LastName.ToLower().StartsWith(partOfLastName)).Select(p => p.LastName).Distinct().ToList();
        }
        public List<string> GetPhysicalPersonName(string partOfName, string lastName)
        {
            return entitty.PhysicalPersons.Where(p => p.Name.ToLower().StartsWith(partOfName) && p.LastName.ToLower() == lastName).Select(p => p.Name).Distinct().ToList();
        }
        public List<JSON_Person> GetPhysicalPersonSurname(string partOfSurname, string lastName, string name)
        {
            return entitty.PhysicalPersons.Where(p => p.Surname.ToLower().StartsWith(partOfSurname) && p.LastName.ToLower() == lastName && p.Name.ToLower() == name).AsEnumerable().Select(p => new JSON_Person()
             {
                 LastName = p.LastName,
                 Name = p.Name,
                 PersonApartment = p.Apartment,
                 PersonBirthdayYear = p.YearOfBirthday,
                 PersonBuild = p.Build,
                 PersonCity = p.Street.City.Name,
                 PersonDistrict = p.Street.City.District.Name,
                 PersonRegion = p.Street.City.District.Region.Name,
                 PersonStreet = p.Street.Name,
                 PersonDateOfIssueСertificate = p.DateOfIssueСertificate.ToString("dd'/'MM'/'yyyy"),
                 PersonIssuedBy = p.СertificateIssuedBy,
                 PersonNumberСertificate = p.СertificateNumber,
                 PersonPhoneNumber = p.PhoneNumber,
                 PersonPostIndex = p.PostIndex,
                 PersonSeriesСertificate = p.SeriesСertificate,
                 Surname = p.Surname
             }).ToList();
        }

        public JSON_Jurist GetJuristicPerson(string code)
        {
          return entitty.JuridicalPersons.Where(j => j.Code.ToLower() == code).Select(j => new JSON_Jurist()
            {
                Apartment = j.Apartment,
                Build = j.Build,
                City = j.Street.City.Name,
                Code = j.Code,
                CostAcquisition = j.CostAcquisition,
                CostAcquisitionInPercent = j.CostAcquisitionInPercent,
                District = j.Street.City.District.Name,
                FullName = j.Name,
                IsInvestmentFund = j.IsInvestmentFund,
                PhoneNumber = j.PhoneNumber,
                PostIndex = j.PostIndex,
                Region = j.Street.City.District.Region.Name,
                ShareStateProperty = j.ShareStateProperty,
                Street = j.Street.Name,
                TotalAssets = j.TotalAssets
            }
                ).FirstOrDefault();

        }

        public List<VAT> GetVatFrimInfo()
        {
            return entitty.VATs.ToList<VAT>();
        }

        public JSON_Region GetRegion(string code)
        {
            return entitty.Regions.Where(r => r.Code == code).Select(r => new JSON_Region { ID = r.ID, Name = r.Name }).FirstOrDefault();
        }


        public void TryUpdateAuction(int aucID, Auction auc)
        {
            Auction findauc = GetAuctionByID(aucID);
            if (findauc.DateStart != auc.DateStart)
                findauc.DateStart = auc.DateStart;
            if (findauc.DateEnd != auc.DateEnd)
                findauc.DateEnd = auc.DateEnd;
            entitty.SaveChanges();
        }
        public int GetAuctionID(string Number)
        {
            return entitty.Auctions.Where(a => a.Number == Number).FirstOrDefault().ID;
        }
        public Auction GetCurrentAuction()
        {
            return entitty.Auctions.Where(a => a.ID == 1).FirstOrDefault();
        }
        public Auction GetAuctionByID(int id)
        {
            return entitty.Auctions.Where(a => a.ID == id).FirstOrDefault();
        }

        public List<VATModel> GetVats()
        {
            List<VATModel> lVat = entitty.VATs.Select(v => new VATModel
            {
                ID = v.ID,
                Apartment = v.Apartment,
                BranchCode = v.BranchCode,
                Build = v.Build,
                Street = v.Street.Name,
                City = v.Street.City.Name,
                District = v.Street.City.District.Name,
                Code = v.Code,
                FORMVIP = v.FORMVIP,
                ManagerFIO = v.ManagerFIO,
                MIN_CODE = v.MIN_CODE,
                Name = v.Name,
                NUMEMPL = v.NUMEMPL,
                PhoneNumber = v.PhoneNumber,
                REG_OWN = v.REG_OWN,
                REGISTRAT = v.REGISTRAT,
                PostIndex = v.PostIndex,
                TotalAssets = v.TotalAssets,
                Region = v.Street.City.District.Region.Name,
                TYPEPRIV = v.TYPEPRIV
            }).ToList();
            return lVat;
        }

        public string GetVATCode(int id)
        {
            return entitty.VATs.Where(v => v.ID == id).FirstOrDefault().Code;
        }

        public VATListModel GetVatsOfCurrentAuction()
        {
            List<VATModel> lVat = (from vatcol in entitty.VAT_AuctionCollection
                                   join auc in entitty.Auctions on vatcol.AuctionID equals auc.ID
                                   join v in entitty.VATs on vatcol.VATID equals v.ID
                                   where auc.ID == 1
                                   select new VATModel
                                   {
                                       ID = v.ID,
                                       Apartment = v.Apartment,
                                       BranchCode = v.BranchCode,
                                       Build = v.Build,
                                       Street = v.Street.Name,
                                       City = v.Street.City.Name,
                                       District = v.Street.City.District.Name,
                                       Code = v.Code,
                                       FORMVIP = v.FORMVIP,
                                       ManagerFIO = v.ManagerFIO,
                                       MIN_CODE = v.MIN_CODE,
                                       Name = v.Name,
                                       NUMEMPL = v.NUMEMPL,
                                       PhoneNumber = v.PhoneNumber,
                                       REG_OWN = v.REG_OWN,
                                       REGISTRAT = v.REGISTRAT,
                                       PostIndex = v.PostIndex,
                                       TotalAssets = v.TotalAssets,
                                       Region = v.Street.City.District.Region.Name,
                                       TYPEPRIV = v.TYPEPRIV,
                                       StockQuantity = vatcol.StockQuantity,
                                       RatedCost = vatcol.RatedCost,
                                       BeginCost=vatcol.BeginCost
                                   }).ToList();
            VATListModel vlm = new VATListModel() { list = lVat, aucNum = GetCurrentAuctionNumber() };

            return vlm;
        }

        public VATModel GetVATinAuction(int id, string aucnum)
        {
            VATModel Vat = (from vatcol in entitty.VAT_AuctionCollection
                            join auc in entitty.Auctions on vatcol.AuctionID equals auc.ID
                            join v in entitty.VATs on vatcol.VATID equals v.ID
                            where auc.Number == aucnum && v.ID == id
                            select new VATModel
                            {
                                ID = v.ID,
                                Apartment = v.Apartment,
                                BranchCode = v.BranchCode,
                                Build = v.Build,
                                Street = v.Street.Name,
                                City = v.Street.City.Name,
                                District = v.Street.City.District.Name,
                                Code = v.Code,
                                FORMVIP = v.FORMVIP,
                                ManagerFIO = v.ManagerFIO,
                                MIN_CODE = v.MIN_CODE,
                                Name = v.Name,
                                NUMEMPL = v.NUMEMPL,
                                PhoneNumber = v.PhoneNumber,
                                REG_OWN = v.REG_OWN,
                                REGISTRAT = v.REGISTRAT,
                                PostIndex = v.PostIndex,
                                TotalAssets = v.TotalAssets,
                                Region = v.Street.City.District.Region.Name,
                                TYPEPRIV = v.TYPEPRIV,
                                StockQuantity = vatcol.StockQuantity,
                                RatedCost = vatcol.RatedCost,
                                BeginCost = vatcol.BeginCost
                            }).FirstOrDefault();
            return Vat;
        }

        public VATListModel GetVatsOfAuction(string number)
        {
            List<VATModel> lVat = (from vatcol in entitty.VAT_AuctionCollection
                                   join auc in entitty.Auctions on vatcol.AuctionID equals auc.ID
                                   join v in entitty.VATs on vatcol.VATID equals v.ID
                                   where auc.Number == number
                                   select new VATModel
                                   {
                                       ID = v.ID,
                                       Apartment = v.Apartment,
                                       BranchCode = v.BranchCode,
                                       Build = v.Build,
                                       Street = v.Street.Name,
                                       City = v.Street.City.Name,
                                       District = v.Street.City.District.Name,
                                       Code = v.Code,
                                       FORMVIP = v.FORMVIP,
                                       ManagerFIO = v.ManagerFIO,
                                       MIN_CODE = v.MIN_CODE,
                                       Name = v.Name,
                                       NUMEMPL = v.NUMEMPL,
                                       PhoneNumber = v.PhoneNumber,
                                       REG_OWN = v.REG_OWN,
                                       REGISTRAT = v.REGISTRAT,
                                       PostIndex = v.PostIndex,
                                       TotalAssets = v.TotalAssets,
                                       Region = v.Street.City.District.Region.Name,
                                       TYPEPRIV = v.TYPEPRIV,
                                       StockQuantity = vatcol.StockQuantity,
                                       RatedCost = vatcol.RatedCost,
                                       BeginCost = vatcol.BeginCost
                                   }).ToList();
            VATListModel vlm = new VATListModel() { list = lVat, aucNum = GetCurrentAuctionNumber() };

            return vlm;
        }

    
        public VATModel GetVAT(int id)
        {
            VATModel Vat = entitty.VATs.Where(v => v.ID == id).Select(v => new VATModel
              {
                  ID = v.ID,
                  Apartment = v.Apartment,
                  BranchCode = v.BranchCode,
                  Build = v.Build,
                  Street = v.Street.Name,
                  City = v.Street.City.Name,
                  District = v.Street.City.District.Name,
                  Code = v.Code,
                  FORMVIP = v.FORMVIP,
                  ManagerFIO = v.ManagerFIO,
                  MIN_CODE = v.MIN_CODE,
                  Name = v.Name,
                  NUMEMPL = v.NUMEMPL,
                  PhoneNumber = v.PhoneNumber,
                  REG_OWN = v.REG_OWN,
                  REGISTRAT = v.REGISTRAT,
                  PostIndex = v.PostIndex,
                  TotalAssets = v.TotalAssets,
                  Region = v.Street.City.District.Region.Name,
                  TYPEPRIV = v.TYPEPRIV,
                
              }).FirstOrDefault();
            return Vat;
        }

        public int GetVATIDByCode(string code)
        {
            VAT vat = entitty.VATs.Where(v => v.Code == code).FirstOrDefault();
            return vat!=null?vat.ID:0;
        }

      public string GetCurrentAuctionNumber()
      {
          return entitty.Auctions.Where(a => a.ID == 1).FirstOrDefault().Number;
      }

      public string GetNextAuctionRequestNumberForJurRequest(int aucid)
      {
          List<JuristRequest> listjreq = (from vac in entitty.VAT_AuctionCollection
                                          join jreq in entitty.JuridicalPersonRequests on vac.ID equals jreq.VAT_AuctionCollectionID
                                          where vac.AuctionID == aucid
                                          select new JuristRequest() 
                                          { 
                                              RequestNumberRegistrator = jreq.RequestNumberRegistrator,
                                              RequestNumberNumber = jreq.RequestNumberNumber
                                          }).ToList();
          int res=1;
          List<int> numlist = new List<int>();
          foreach (JuristRequest jr in listjreq)
          {
              if (jr.RequestNumberRegistrator == "50")
              {
                  int.TryParse(jr.RequestNumberNumber, out res);
                  numlist.Add(res);
              }
          }
           string yournum;
           if (numlist.Count == 0)
               yournum = "0001";
          
          yournum = (numlist.Max() + 1).ToString();

          switch (yournum.Length)
         {
             case 1:
                 yournum = "000" + yournum;
                 break;
             case 2:
                 yournum = "00" + yournum;
                 break;
             case 3:
                 yournum = "0" + yournum;
                 break;
         }
         return yournum;
                 
      }

      public string GetNextAuctionRequestNumberForPhysRequest(int aucid)
      {
          List<PersonRequest> listpreq = (from vac in entitty.VAT_AuctionCollection
                                          join preq in entitty.PhysicalPersonRequests on vac.ID equals preq.VAT_AuctionCollectionID
                                          where vac.AuctionID == aucid
                                          select new PersonRequest()
                                          {
                                              RequestNumberRegistrator = preq.RequestNumberRegistrator,
                                              RequestNumberNumber = preq.RequestNumberNumber
                                          }).ToList();
          int res = 1;
          List<int> numlist = new List<int>();
          foreach (PersonRequest pr in listpreq)
          {
              if (pr.RequestNumberRegistrator == "01")
              {
                  int.TryParse(pr.RequestNumberNumber, out res);
                  numlist.Add(res);
              }
          }
          string yournum;
          if (numlist.Count == 0)
              yournum = "0001";

          yournum = (numlist.Max() + 1).ToString();

          switch (yournum.Length)
          {
              case 1:
                  yournum = "000" + yournum;
                  break;
              case 2:
                  yournum = "00" + yournum;
                  break;
              case 3:
                  yournum = "0" + yournum;
                  break;
          }
          return yournum;

      }
        //public List<TenderResultModel> GetTenderResult(int tenderID)
        //{


        //    var t = (from tender in entitty.Tenders
        //             join firm in entitty.VATFirms on tender.VATFirmID equals firm.ID
        //             join order in entitty.Orders on tender.ID equals order.TenderID
        //             join buyer in entitty.Buyers on order.BuyerID equals buyer.ID
        //             where tender.ID == tenderID
        //             select new
        //             {
        //                 BuyerName = buyer.Name,
        //                 RatedCost = tender.RatedCost,
        //                 FirmName = firm.Name,
        //                 TenderID = tender.ID,
        //                 orderAmount = order.Amount

        //             }).ToList();
        //    List<TenderResultModel> listRes = new List<TenderResultModel>();
        //    foreach (var i in t)
        //    {
        //        listRes.Add(new TenderResultModel() { BuyerName = i.BuyerName, FactCost = GetFactCost(i.TenderID), FirmName = i.FirmName, RatedCost = i.RatedCost, StockQuantity = GetStockCount(i.orderAmount, i.TenderID) });
        //    }
        //    return listRes;
        //}

        //public IList<TenderInfoModel> GetTendersInfo()
        //{
        //    //return (from tender in entitty.Tenders
        //    //        join firm in entitty.VATFirms on tender.VATFirmID equals firm.ID
        //    //        select new TenderInfoModel
        //    //         {
        //    //             tenderID = tender.ID,
        //    //             firmName = firm.Name,
        //    //             stockQuantity = tender.Quantity,
        //    //             ratedCost = tender.RatedCost
        //    //         }).ToList();
        //}

        #endregion

        #region Insert Queries
        public void AddVatToAuction(List<VATModel> vm, int aucID)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                foreach (VATModel vat in vm)
                {
                    VAT_AuctionCollection vatCol = new VAT_AuctionCollection();
                    vatCol.AuctionID = aucID;
                    vatCol.StockQuantity = vat.StockQuantity;
                    vatCol.RatedCost = vat.RatedCost;
                    entitty.AddToVAT_AuctionCollection(vatCol);
                    entitty.SaveChanges();
                }
                scope.Complete();
            }
        }

        public int AddPhysicalPerson(PersonRequest pr)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                PhysicalPerson per = new PhysicalPerson();
                per.LastName = pr.PersonLastName;
                per.Name = pr.PersonName;
                per.Surname = pr.PersonSurname;
                per.CostAcquisition = pr.PersonCostAcquisition;
                per.CostAcquisitionInPercent = pr.PersonCostAcquisitionInPercent;
                per.YearOfBirthday = pr.PersonBirthdayYear;
                per.PostIndex = pr.PersonPostIndex;
                per.SeriesСertificate = pr.PersonSeriesСertificate;
                per.СertificateIssuedBy = pr.PersonIssuedBy;
                per.СertificateNumber = pr.PersonNumberСertificate;
                per.СertificateType = pr.PersonСertificate;
                per.DateOfIssueСertificate = pr.PersonDateOfIssueСertificate;
                per.PhoneNumber = pr.PersonPhoneNumber;
                Region reg = entitty.Regions.Where(r => r.Name == pr.PersonRegion).FirstOrDefault();
                District dis = entitty.Districts.Where(d => d.Name == pr.PersonDistrict && d.RegionID == reg.ID).FirstOrDefault();
                City city = entitty.Cities.Where(c => c.Name == pr.PersonCity).FirstOrDefault();
                if (city == null)
                {
                    city = new City() { Name = pr.PersonCity, DistrictID = dis.ID };
                    entitty.AddToCities(city);
                    entitty.SaveChanges();
                }
                Street street = entitty.Streets.Where(s => s.Name == pr.PersonStreet && s.CityID == city.ID).FirstOrDefault();
                if (street == null)
                {
                    street = new Street() { Name = pr.PersonStreet, CityID = city.ID };
                    entitty.AddToStreets(street);
                    entitty.SaveChanges();
                }
                per.StreetID = street.ID;
                per.Build = pr.PersonBuild;
                per.Apartment = pr.PersonApartment ?? null;
                entitty.AddToPhysicalPersons(per);
                entitty.SaveChanges();
                PhysicalPersonRequest physreq = new PhysicalPersonRequest();
                physreq.BankName = pr.BankName;
                physreq.ExpenseOfCustomer = pr.ExpenseOfCustomer;
                physreq.VAT_AuctionCollectionID = (from vat in entitty.VATs
                                                          where vat.Code == pr.VATCode
                                                          join kep_vat in entitty.Keeper_VAT
                                                          on vat.ID equals kep_vat.ID
                                                          join kep_vat_auccoll in entitty.VAT_AuctionCollection
                                                          on vat.ID equals kep_vat_auccoll.VATID
                                                          select kep_vat_auccoll.ID).SingleOrDefault();
                physreq.MFO = pr.MFO;
                physreq.Summ = pr.Summ;
                physreq.PhysicalPersonID = per.ID;
                //fill request number
                physreq.RequestNumber = pr.RequestNumber;
                physreq.RequestNumberAuction = pr.RequestNumberAuction;
                physreq.RequestNumberNumber = pr.RequestNumberNumber;
                physreq.RequestNumberPPZ = pr.RequestNumberPPZ;
                physreq.RequestNumberRegion = pr.RequestNumberRegion;
                physreq.RequestNumberRegistrator = pr.RequestNumberRegistrator;
                //------------------
                entitty.AddToPhysicalPersonRequests(physreq);
                entitty.SaveChanges();
                scope.Complete();
                return per.ID;
            }
        }

        public void AddRepresentativeOfPhysicalPerson(PersonRequest pr, int PersonID)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                RepresentativeOfPhysicalPerson agent = new RepresentativeOfPhysicalPerson();
                agent.LastName = pr.AgentLastName;
                agent.Name = pr.AgentName;
                agent.Surname = pr.AgentSurname;
                agent.PostIndex = pr.AgentPostIndex;
                agent.SeriesСertificate = pr.AgentSeriesСertificate;
                agent.СertificateIssuedBy = pr.AgentIssuedBy;
                agent.СertificateNumber = pr.AgentNumberСertificate;
                agent.СertificateType = pr.AgentСertificate;
                agent.DateOfIssueСertificate = pr.AgentDateOfIssueСertificate;
                agent.PhoneNumber = pr.AgentPhoneNumber;
                agent.DocumentOfConfirm = pr.DocumentOfConfirm;
                agent.DateDocumentOfConfirm = pr.DateDocumentOfConfirm;
                Region reg = entitty.Regions.Where(r => r.Name == pr.AgentRegion).FirstOrDefault();
                District dis = entitty.Districts.Where(d => d.Name == pr.AgentDistrict && d.RegionID == reg.ID).FirstOrDefault();
                City city = entitty.Cities.Where(c => c.Name == pr.PersonCity).FirstOrDefault();
                if (city == null)
                {
                    city = new City() { Name = pr.PersonCity, DistrictID = dis.ID };
                    entitty.AddToCities(city);
                    entitty.SaveChanges();
                }
                Street street = entitty.Streets.Where(s => s.Name == pr.PersonStreet && s.CityID == city.ID).FirstOrDefault();
                if (street == null)
                {
                    street = new Street() { Name = pr.PersonStreet, CityID = city.ID };
                    entitty.AddToStreets(street);
                    entitty.SaveChanges();
                }
                agent.StreetID = street.ID;
                agent.Build = pr.PersonBuild;
                agent.Apartment = (pr.PersonApartment == null) ? null : pr.PersonApartment;
                agent.PhysicalPersonID = PersonID;
                entitty.AddToRepresentativeOfPhysicalPersons(agent);
                entitty.SaveChanges();
                scope.Complete();
            }

        }

        public int AddJuridicalPerson(JuristRequest jr)
        {
            using (TransactionScope scope = new TransactionScope())
            {
                JuridicalPerson jur = new JuridicalPerson();
                jur.Code = jr.JFCode;
                jur.Name = jr.JFFullName;
                jur.PhoneNumber = jr.JFPhoneNumber;
                jur.PostIndex = jr.JFPostIndex;
                jur.ShareStateProperty = jr.JFShareStateProperty;
                jur.TotalAssets = jr.JFTotalAssets;
                jur.CostAcquisition = jr.JFCostAcquisition;
                jur.CostAcquisitionInPercent = jr.JFCostAcquisitionInPercent;
                jur.IsInvestmentFund = (jr.JFIsInvestmentFund) ? true : false;

                Region reg = entitty.Regions.Where(r => r.Name == jr.JFRegion).FirstOrDefault();
                District dis = entitty.Districts.Where(d => d.Name == jr.JFDistrict && d.RegionID == reg.ID).FirstOrDefault();
                City city = entitty.Cities.Where(c => c.Name == jr.JFCity).FirstOrDefault();
                if (city == null)
                {
                    city = new City() { Name = jr.JFCity, DistrictID = dis.ID };
                    entitty.AddToCities(city);
                    entitty.SaveChanges();
                }
                Street street = entitty.Streets.Where(s => s.Name == jr.JFStreet && s.CityID == city.ID).FirstOrDefault();
                if (street == null)
                {
                    street = new Street() { Name = jr.JFStreet, CityID = city.ID };
                    entitty.AddToStreets(street);
                    entitty.SaveChanges();
                }
                jur.StreetID = street.ID;
                jur.Build = jr.JFBuild;
                jur.Apartment = jr.JFApartment;
                entitty.AddToJuridicalPersons(jur);
                entitty.SaveChanges();

                JuridicalPersonRequest jpr = new JuridicalPersonRequest();
                jpr.BankName = jr.BankName;
                jpr.ChiefFIO = jr.ChiefFIO;
                jpr.ExpenseOfCustomer = jr.ExpenseOfCustomer;
                jpr.MFO = jr.MFO;
                jpr.Summ = jr.Summ;
                jpr.ChiefFIO = jr.ChiefFIO;
                jpr.VAT_AuctionCollectionID = (from vat in entitty.VATs
                                                      where vat.Code == jr.VATCode
                                                      join kep_vat in entitty.Keeper_VAT
                                                      on vat.ID equals kep_vat.ID
                                                      join kep_vat_auccoll in entitty.VAT_AuctionCollection
                                                      on vat.ID equals kep_vat_auccoll.VATID
                                                      select kep_vat_auccoll.ID).SingleOrDefault();
                jpr.JuridicalPersonID = jur.ID;
                //fill request number
                jpr.RequestNumber = jr.RequestNumber;
                jpr.RequestNumberAuction = jr.RequestNumberAuction;
                jpr.RequestNumberNumber = jr.RequestNumberNumber;
                jpr.RequestNumberPPZ = jr.RequestNumberPPZ;
                jpr.RequestNumberRegion = jr.RequestNumberRegion;
                jpr.RequestNumberRegistrator = jr.RequestNumberRegistrator;
                //------------------

                entitty.AddToJuridicalPersonRequests(jpr);
                entitty.SaveChanges();
                scope.Complete();
                return jur.ID;
            }
        }

        public void AddRepresentativeOfJuridicalPerson(JuristRequest jr, int JuristID)
        {
            RepresentativeOfJuridicalPerson agent = new RepresentativeOfJuridicalPerson();
            agent.LastName = jr.PersonLastName;
            agent.Name = jr.PersonName;
            agent.Surname = jr.PersonSurname;
            agent.СertificateType = jr.PersonСertificate;
            agent.SeriesСertificate = jr.PersonSeriesСertificate;
            agent.СertificateNumber = jr.PersonNumberСertificate;
            agent.JuridicalPersonID = JuristID;
            entitty.AddToRepresentativeOfJuridicalPersons(agent);
            entitty.SaveChanges();
        }

        public void AddVAT(VATModel vm)
        {

            using (TransactionScope scope = new TransactionScope())
            {
                if (entitty.VATs.Where(v => v.Code == vm.Code).FirstOrDefault() != null)
                    return;
                VAT vat = new VAT();
                if (!String.IsNullOrEmpty(vm.BranchCode))
                {
                    Branch branch = entitty.Branches.Where(b => b.Code == vm.BranchCode).FirstOrDefault();
                    if (branch != null)
                        vat.BranchID = branch.ID;

                    vat.BranchCode = vm.BranchCode;
                }
                vat.Build = vm.Build;
                vat.Apartment = vm.Apartment;
                Region reg = entitty.Regions.Where(r => r.Name == vm.Region).FirstOrDefault();
                District dis = entitty.Districts.Where(d => d.Name == vm.District && d.RegionID == reg.ID).FirstOrDefault();
                City city = entitty.Cities.Where(c => c.Name == vm.City && c.DistrictID == dis.ID).FirstOrDefault();
                if (city == null)
                {
                    city = new City() { Name = vm.City, DistrictID = dis.ID };
                    entitty.AddToCities(city);
                    entitty.SaveChanges();
                }
                Street street = entitty.Streets.Where(s => s.Name == vm.Street && s.CityID == city.ID).FirstOrDefault();
                if (street == null)
                {

                    street = new Street() { Name = (string.IsNullOrEmpty(vm.Street)) ? "-" : vm.Street, CityID = city.ID };
                    entitty.AddToStreets(street);
                    entitty.SaveChanges();
                }
                vat.StreetID = street.ID;
                vat.TotalAssets = vm.TotalAssets;
                vat.Code = vm.Code;
                vat.TYPEPRIV = vm.TYPEPRIV;
                vat.FORMVIP = vm.FORMVIP;
                vat.ManagerFIO = vm.ManagerFIO;
                vat.MIN_CODE = vm.MIN_CODE;
                vat.Name = vm.Name;
                vat.NUMEMPL = vm.NUMEMPL;
                vat.PhoneNumber = vm.PhoneNumber;
                vat.PostIndex = vm.PostIndex;
                vat.REG_OWN = vm.REG_OWN;
                vat.REGISTRAT = vm.REGISTRAT;
                entitty.AddToVATs(vat);
                entitty.SaveChanges();
                scope.Complete();

            }
        }

        public void UpdateVAT(VATModel vm,string anum)
        {

            using (TransactionScope scope = new TransactionScope())
            {
               VAT vat= entitty.VATs.Where(v => v.ID == vm.ID).FirstOrDefault();
                 if(vat==null)return;   
                 
                if (!String.IsNullOrEmpty(vm.BranchCode))
                {
                    Branch branch = entitty.Branches.Where(b => b.Code == vm.BranchCode).FirstOrDefault();
                    if (branch != null)
                        vat.BranchID = branch.ID;

                    vat.BranchCode = vm.BranchCode;
                }
                vat.Build = vm.Build;
                vat.Apartment = vm.Apartment;
                Region reg = entitty.Regions.Where(r => r.Name == vm.Region).FirstOrDefault();
                District dis = entitty.Districts.Where(d => d.Name == vm.District && d.RegionID == reg.ID).FirstOrDefault();
                City city = entitty.Cities.Where(c => c.Name == vm.City && c.DistrictID == dis.ID).FirstOrDefault();
                if (city == null)
                {
                    city = new City() { Name = vm.City, DistrictID = dis.ID };
                    entitty.AddToCities(city);
                    entitty.SaveChanges();
                }
                Street street = entitty.Streets.Where(s => s.Name == vm.Street && s.CityID == city.ID).FirstOrDefault();
                if (street == null)
                {

                    street = new Street() { Name = (string.IsNullOrEmpty(vm.Street)) ? "-" : vm.Street, CityID = city.ID };
                    entitty.AddToStreets(street);
                    entitty.SaveChanges();
                }
                vat.StreetID = street.ID;
                vat.TotalAssets = vm.TotalAssets;
                vat.Code = vm.Code;
                vat.TYPEPRIV = vm.TYPEPRIV;
                vat.FORMVIP = vm.FORMVIP;
                vat.ManagerFIO = vm.ManagerFIO;
                vat.MIN_CODE = vm.MIN_CODE;
                vat.Name = vm.Name;
                vat.NUMEMPL = vm.NUMEMPL;
                vat.PhoneNumber = vm.PhoneNumber;
                vat.PostIndex = vm.PostIndex;
                vat.REG_OWN = vm.REG_OWN;
                vat.REGISTRAT = vm.REGISTRAT;
               
               VAT_AuctionCollection vatauccol = entitty.VAT_AuctionCollection.Where(av => av.Auction.Number == anum).FirstOrDefault();
               vatauccol.RatedCost = vm.RatedCost;
               vatauccol.BeginCost = vm.BeginCost;
               vatauccol.StockQuantity = vm.StockQuantity;
               entitty.SaveChanges();
               scope.Complete();
                
            }
        }







        #endregion










        /////////////////////////////////Excel Dal///////////////////////////////////////////////////////
      //#region ExcelConnection
      //  static OleDbConnection oledbConn = null;

      //  private static OleDbConnection ExcelInstance(string filepath)
      //  {
      //      if (oledbConn == null)
      //      {
      //          string pathTodirectory = Path.GetDirectoryName(filepath);
      //          oledbConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + pathTodirectory + "; Extended Properties=dBASE III;");
      //      }
      //      return oledbConn;
      //  }

      //  //public void ReadDBFFileBank(string filepath)
      //  //{
      //  //    OleDbConnection cn = ExcelInstance(filepath);
      //  //    using (OleDbCommand cm = cn.CreateCommand())
      //  //    {
      //  //        string filename = Path.GetFileName(filepath);
      //  //        //Insert into BankFile Table
      //  //        BankFile bf = new BankFile();

      //  //        bf = entitty.BankFiles.Where(b => b.Name == filename).FirstOrDefault();
      //  //        if (bf == null)
      //  //        {
      //  //            bf = new BankFile();
      //  //            bf.Name = filename;
      //  //            entitty.AddToBankFiles(bf);
      //  //            entitty.SaveChanges();
      //  //        }
      //  //        else
      //  //        {
      //  //            List<BankFileField> listBff = new List<BankFileField>();
      //  //            listBff = entitty.BankFileFields.Where(ff => ff.BankFileID == bf.ID).ToList<BankFileField>();
      //  //            foreach (BankFileField bfitem in listBff)
      //  //            {
      //  //                entitty.BankFileFields.DeleteObject(bfitem);
      //  //            }
      //  //            entitty.SaveChanges();
      //  //        }
      //  //        //-------------------------//
      //  //        cn.Open();
      //  //        cm.CommandText = "SELECT * FROM " + filename;
      //  //        OleDbDataReader dr = cm.ExecuteReader();
      //  //        //Insert into BankFileFields Table
      //  //        while (dr.Read())
      //  //        {
      //  //            BankFileField bff = new BankFileField();
      //  //            bff.ACCCLI = dr["ACCCLI"].ToString();
      //  //            bff.MFOCLI = dr["MFOCLI"].ToString();
      //  //            bff.OKPOCLI = dr["OKPOCLI"].ToString();
      //  //            bff.NAMECLI = dr["NAMECLI"].ToString();
      //  //            bff.BANKCLI = dr["BANKCLI"].ToString();
      //  //            bff.ACCCOR = dr["ACCCOR"].ToString();
      //  //            bff.MFOCOR = dr["MFOCOR"].ToString();
      //  //            bff.OKPOCOR = dr["OKPOCOR"].ToString();
      //  //            bff.NAMECOR = dr["NAMECOR"].ToString();
      //  //            bff.BANKCOR = dr["BANKCOR"].ToString();
      //  //            bff.NDOC = dr["NDOC"].ToString();
      //  //            bff.SUMMA = Convert.ToInt32(dr["SUMMA"]);
      //  //            bff.NAZN = dr["NAZN"].ToString();
      //  //            bff.DT = dr["DT"].ToString();
      //  //            bff.DK = dr["DK"].ToString();
      //  //            bff.TDOC = dr["TDOC"].ToString();
      //  //            bff.DV = dr["DV"].ToString();
      //  //            bff.IMPFLG = dr["IMPFLG"].ToString();
      //  //            bff.BankFileID = bf.ID;
      //  //            entitty.AddToBankFileFields(bff);

      //  //            entitty.SaveChanges();
      //  //        }
      //  //        //------------------------------------//
      //  //        cn.Close();
      //  //    }
      //  //}


      //  public List<VATModel> ReadDBFFileVAT(string filepath)
      //  {
      //      OleDbConnection cn = ExcelInstance(filepath);
      //      using (OleDbCommand cm = cn.CreateCommand())
      //      {
      //          string filename = Path.GetFileName(filepath);
      //          cn.Open();
      //          cm.CommandText = "SELECT * FROM " + filename;
      //          OleDbDataReader dr = cm.ExecuteReader();


      //          List<VATModel> listVAT = new List<VATModel>();

      //          while (dr.Read())
      //          {
      //              string managerFIO = dr["MANAGER"].ToString();
      //              if (managerFIO.IndexOf("КIШУК АНАТОЛIЙ СЕРГIЙОВИЧ") != -1)
      //                  managerFIO = "КIШУК АНАТОЛIЙ СЕРГIЙОВИЧ";
      //              string fullAddress = dr["ADDRESS1"].ToString() + dr["ADDRESS2"].ToString();
      //              if (fullAddress.IndexOf("М.ЛУГАНСЬК, ВУЛ.ФРУНЗЕ, 119") != -1)
      //                  fullAddress = "М.ЛУГАНСЬК, ВУЛ.ФРУНЗЕ, 119";
      //              listVAT.Add(new VATModel()
      //              {
      //                  Code = dr["ENT_CODE"].ToString(),
      //                  BranchCode = dr["BRANCH"].ToString(),
      //                  Name = dr["ENT_NAME"].ToString().Replace("Ў", "І"),
      //                  PostIndex = dr["INDEX"].ToString(),
      //                  FullAddressDBF = fullAddress,
      //                  PhoneNumber = dr["PHONE"].ToString(),
      //                  ManagerFIO = managerFIO,
      //                  TotalAssets = Convert.ToDouble(dr["CAPITAL"]),
      //                  Region = dr["OBL_CODE"].ToString(),
      //                  FORMVIP = dr["FORMVIP"].ToString(),
      //                  MIN_CODE = dr["MIN_CODE"].ToString(),
      //                  NUMEMPL = dr["NUMEMPL"].ToString(),
      //                  REG_OWN = Convert.ToBoolean(dr["REG_OWN"]),
      //                  REGISTRAT = dr["REGISTRAT"].ToString(),
      //                  TYPEPRIV = dr["TYPEPRIV"].ToString()
      //              });
      //          }
      //          //------------------------------------//
      //          cn.Close();
      //          return listVAT;

      //      }
      //  }

      //  //public void ReadDBFFileBrach(string filepath)
      //  //{
      //  //    OleDbConnection cn = ExcelInstance(filepath);
      //  //    using (OleDbCommand cm = cn.CreateCommand())
      //  //    {
      //  //        string filename = Path.GetFileName(filepath);

      //  //        cn.Open();
      //  //        cm.CommandText = "SELECT * FROM " + filename;
      //  //        OleDbDataReader dr = cm.ExecuteReader();
      //  //        //Insert into BankFileFields Table
      //  //        int id = 1;
      //  //        while (dr.Read())
      //  //        {
      //  //            Branch branch = new Branch();
      //  //            branch.Code = dr["BR_CODE"].ToString();
      //  //            branch.Name = dr["NAME"].ToString();
      //  //            entitty.AddToBranches(branch);
      //  //            entitty.SaveChanges();
      //  //            id++;
      //  //        }
      //  //        //------------------------------------//
      //  //        cn.Close();

      //  //    }
      //  //}
      //   #endregion

        #region SAGK
        public int getNumGagk() {
            return 115;
        }
        #endregion
    }
}